{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3",
   "language": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('港股打新人数.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "        上市日期  申购人数\n",
       "0 2015-01-09  1668\n",
       "1 2015-01-13   704\n",
       "2 2015-01-14  3338\n",
       "3 2015-01-15  3662\n",
       "4 2015-01-15  1618"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>上市日期</th>\n      <th>申购人数</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2015-01-09</td>\n      <td>1668</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2015-01-13</td>\n      <td>704</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2015-01-14</td>\n      <td>3338</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2015-01-15</td>\n      <td>3662</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2015-01-15</td>\n      <td>1618</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(df.values.T,)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                   0                    1                    2    \\\n",
       "0  2015-01-09 00:00:00  2015-01-13 00:00:00  2015-01-14 00:00:00   \n",
       "1                 1668                  704                 3338   \n",
       "\n",
       "                   3                    4                    5    \\\n",
       "0  2015-01-15 00:00:00  2015-01-15 00:00:00  2015-01-15 00:00:00   \n",
       "1                 3662                 1618                  784   \n",
       "\n",
       "                   6                    7                    8    \\\n",
       "0  2015-01-16 00:00:00  2015-01-30 00:00:00  2015-02-13 00:00:00   \n",
       "1                 1196                 1226                  422   \n",
       "\n",
       "                   9    ...                  833                  834  \\\n",
       "0  2015-03-11 00:00:00  ...  2021-01-15 00:00:00  2021-01-15 00:00:00   \n",
       "1                 3505  ...                35479                26091   \n",
       "\n",
       "                   835                  836                  837  \\\n",
       "0  2021-01-15 00:00:00  2021-01-18 00:00:00  2021-01-19 00:00:00   \n",
       "1               313721                37522                92145   \n",
       "\n",
       "                   838                  839                  840  \\\n",
       "0  2021-01-26 00:00:00  2021-02-04 00:00:00  2021-02-05 00:00:00   \n",
       "1               331495               567475                87981   \n",
       "\n",
       "                   841                  842  \n",
       "0  2021-02-05 00:00:00  2021-02-08 00:00:00  \n",
       "1              1422977               264120  \n",
       "\n",
       "[2 rows x 843 columns]"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n      <th>7</th>\n      <th>8</th>\n      <th>9</th>\n      <th>...</th>\n      <th>833</th>\n      <th>834</th>\n      <th>835</th>\n      <th>836</th>\n      <th>837</th>\n      <th>838</th>\n      <th>839</th>\n      <th>840</th>\n      <th>841</th>\n      <th>842</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2015-01-09 00:00:00</td>\n      <td>2015-01-13 00:00:00</td>\n      <td>2015-01-14 00:00:00</td>\n      <td>2015-01-15 00:00:00</td>\n      <td>2015-01-15 00:00:00</td>\n      <td>2015-01-15 00:00:00</td>\n      <td>2015-01-16 00:00:00</td>\n      <td>2015-01-30 00:00:00</td>\n      <td>2015-02-13 00:00:00</td>\n      <td>2015-03-11 00:00:00</td>\n      <td>...</td>\n      <td>2021-01-15 00:00:00</td>\n      <td>2021-01-15 00:00:00</td>\n      <td>2021-01-15 00:00:00</td>\n      <td>2021-01-18 00:00:00</td>\n      <td>2021-01-19 00:00:00</td>\n      <td>2021-01-26 00:00:00</td>\n      <td>2021-02-04 00:00:00</td>\n      <td>2021-02-05 00:00:00</td>\n      <td>2021-02-05 00:00:00</td>\n      <td>2021-02-08 00:00:00</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1668</td>\n      <td>704</td>\n      <td>3338</td>\n      <td>3662</td>\n      <td>1618</td>\n      <td>784</td>\n      <td>1196</td>\n      <td>1226</td>\n      <td>422</td>\n      <td>3505</td>\n      <td>...</td>\n      <td>35479</td>\n      <td>26091</td>\n      <td>313721</td>\n      <td>37522</td>\n      <td>92145</td>\n      <td>331495</td>\n      <td>567475</td>\n      <td>87981</td>\n      <td>1422977</td>\n      <td>264120</td>\n    </tr>\n  </tbody>\n</table>\n<p>2 rows × 843 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 843 entries, 0 to 842\nData columns (total 2 columns):\n #   Column  Non-Null Count  Dtype         \n---  ------  --------------  -----         \n 0   上市日期    843 non-null    datetime64[ns]\n 1   申购人数    843 non-null    int64         \ndtypes: datetime64[ns](1), int64(1)\nmemory usage: 13.3 KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "error",
     "ename": "ValueError",
     "evalue": "column index (256) not an int in range(256)",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-15-d86e90078ae9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf2\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'people3.xls'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mheader\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mto_excel\u001b[1;34m(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose, freeze_panes)\u001b[0m\n\u001b[0;32m   2173\u001b[0m             \u001b[0minf_rep\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0minf_rep\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2174\u001b[0m         )\n\u001b[1;32m-> 2175\u001b[1;33m         formatter.write(\n\u001b[0m\u001b[0;32m   2176\u001b[0m             \u001b[0mexcel_writer\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2177\u001b[0m             \u001b[0msheet_name\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msheet_name\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\formats\\excel.py\u001b[0m in \u001b[0;36mwrite\u001b[1;34m(self, writer, sheet_name, startrow, startcol, freeze_panes, engine)\u001b[0m\n\u001b[0;32m    728\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    729\u001b[0m         \u001b[0mformatted_cells\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_formatted_cells\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 730\u001b[1;33m         writer.write_cells(\n\u001b[0m\u001b[0;32m    731\u001b[0m             \u001b[0mformatted_cells\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    732\u001b[0m             \u001b[0msheet_name\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\excel\\_xlwt.py\u001b[0m in \u001b[0;36mwrite_cells\u001b[1;34m(self, cells, sheet_name, startrow, startcol, freeze_panes)\u001b[0m\n\u001b[0;32m     75\u001b[0m                 )\n\u001b[0;32m     76\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 77\u001b[1;33m                 \u001b[0mwks\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwrite\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstartrow\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mcell\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstartcol\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mcell\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcol\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mval\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstyle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     78\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     79\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\xlwt\\Worksheet.py\u001b[0m in \u001b[0;36mwrite\u001b[1;34m(self, r, c, label, style)\u001b[0m\n\u001b[0;32m   1086\u001b[0m            \u001b[1;33m:\u001b[0m\u001b[1;32mclass\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0;31m`\u001b[0m\u001b[1;33m~\u001b[0m\u001b[0mxlwt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mStyle\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mXFStyle\u001b[0m\u001b[0;31m`\u001b[0m \u001b[0mobject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1087\u001b[0m         \"\"\"\n\u001b[1;32m-> 1088\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrow\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwrite\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlabel\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstyle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1089\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1090\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mwrite_rich_text\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mrich_text_list\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstyle\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mStyle\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdefault_style\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\xlwt\\Row.py\u001b[0m in \u001b[0;36mwrite\u001b[1;34m(self, col, label, style)\u001b[0m\n\u001b[0;32m    228\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mwrite\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcol\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlabel\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstyle\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mStyle\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdefault_style\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    229\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__adjust_height\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstyle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 230\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__adjust_bound_col_idx\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcol\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    231\u001b[0m         \u001b[0mstyle_index\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__parent_wb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madd_style\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstyle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    232\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlabel\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mbasestring\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\xlwt\\Row.py\u001b[0m in \u001b[0;36m__adjust_bound_col_idx\u001b[1;34m(self, *args)\u001b[0m\n\u001b[0;32m     71\u001b[0m             \u001b[0miarg\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     72\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;33m(\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m \u001b[1;33m<=\u001b[0m \u001b[0miarg\u001b[0m \u001b[1;33m<=\u001b[0m \u001b[1;36m255\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0marg\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0miarg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 73\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"column index (%r) not an int in range(256)\"\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     74\u001b[0m             \u001b[0msheet\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__parent\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     75\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0miarg\u001b[0m \u001b[1;33m<\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__min_col_idx\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: column index (256) not an int in range(256)"
     ]
    }
   ],
   "source": [
    "df2.to_excel('people3.xls',index=None,header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlsxwriter   #导入模块\n",
    "workbook = xlsxwriter.Workbook('new_excel.xlsx')     #新建excel表\n",
    "worksheet = workbook.add_worksheet('sheet1')       #新建sheet（sheet的名称为\"sheet1\"）\n",
    "headings = ['Number','testA','testB']  \n",
    "   #设置表头\n",
    "data = [\n",
    "    ['2017-9-1','2017-9-2','2017-9-3','2017-9-4','2017-9-5','2017-9-6'],\n",
    "    [10,40,50,20,10,50],\n",
    "    [30,60,70,50,40,30],\n",
    "]                                                              #自己造的数据\n",
    " \n",
    "worksheet.write_row('A1',headings)\n",
    "worksheet.write_column('A2',data[0])\n",
    "worksheet.write_column('B2',data[1])\n",
    "worksheet.write_column('C2',data[2])                   #将数据插入到表格中\n",
    " \n",
    "workbook.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlsxwriter   #导入模块\n",
    "workbook = xlsxwriter.Workbook('new_people.xlsx')     #新建excel表\n",
    "worksheet = workbook.add_worksheet('sheet1')       #新建sheet（sheet的名称为\"sheet1\"）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                  上市日期  申购人数\n",
       "0  2015-01-09 00:00:00  1668\n",
       "1  2015-01-13 00:00:00   704"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>上市日期</th>\n      <th>申购人数</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2015-01-09 00:00:00</td>\n      <td>1668</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2015-01-13 00:00:00</td>\n      <td>704</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index,item in df.iterrows():\n",
    "    date=item['上市日期']\n",
    "    count=item['申购人数']\n",
    "    date=date.replace(' 00:00:00','')\n",
    "    worksheet.write(0,index,date)\n",
    "    worksheet.write(1,index,count)\n",
    "\n",
    "workbook.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 843 entries, 0 to 842\nData columns (total 2 columns):\n #   Column  Non-Null Count  Dtype \n---  ------  --------------  ----- \n 0   上市日期    843 non-null    object\n 1   申购人数    843 non-null    int64 \ndtypes: int64(1), object(1)\nmemory usage: 13.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df= df.set_index('上市日期')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "        上市日期  申购人数\n",
       "0 2015-01-09  1668\n",
       "1 2015-01-13   704\n",
       "2 2015-01-14  3338\n",
       "3 2015-01-15  3662\n",
       "4 2015-01-15  1618"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>上市日期</th>\n      <th>申购人数</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2015-01-09</td>\n      <td>1668</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2015-01-13</td>\n      <td>704</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2015-01-14</td>\n      <td>3338</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2015-01-15</td>\n      <td>3662</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2015-01-15</td>\n      <td>1618</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            申购人数\n",
       "上市日期            \n",
       "2015-01-09  1668\n",
       "2015-01-13   704\n",
       "2015-01-14  3338\n",
       "2015-01-15  3662\n",
       "2015-01-15  1618"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>申购人数</th>\n    </tr>\n    <tr>\n      <th>上市日期</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2015-01-09</th>\n      <td>1668</td>\n    </tr>\n    <tr>\n      <th>2015-01-13</th>\n      <td>704</td>\n    </tr>\n    <tr>\n      <th>2015-01-14</th>\n      <td>3338</td>\n    </tr>\n    <tr>\n      <th>2015-01-15</th>\n      <td>3662</td>\n    </tr>\n    <tr>\n      <th>2015-01-15</th>\n      <td>1618</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m = df.resample('M').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              申购人数\n",
       "上市日期              \n",
       "2015-01-31   14196\n",
       "2015-02-28     422\n",
       "2015-03-31   39179\n",
       "2015-04-30  142838\n",
       "2015-05-31  110068"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>申购人数</th>\n    </tr>\n    <tr>\n      <th>上市日期</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2015-01-31</th>\n      <td>14196</td>\n    </tr>\n    <tr>\n      <th>2015-02-28</th>\n      <td>422</td>\n    </tr>\n    <tr>\n      <th>2015-03-31</th>\n      <td>39179</td>\n    </tr>\n    <tr>\n      <th>2015-04-30</th>\n      <td>142838</td>\n    </tr>\n    <tr>\n      <th>2015-05-31</th>\n      <td>110068</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 40
    }
   ],
   "source": [
    "df_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "line=0\n",
    "for index,item in df_m.iterrows():\n",
    "    # print(index)\n",
    "    # print(type(index))\n",
    "    date=str(index).replace(' 00:00:00','')\n",
    "    date=date[:-3]\n",
    "    count=item['申购人数']\n",
    "    # date=item.index\n",
    "    # print(date)\n",
    "    # print(type(date))\n",
    "    # count=item['申购人数']\n",
    "    # date=date.replace(' 00:00:00','')\n",
    "    worksheet.write(0,line,date)\n",
    "    worksheet.write(1,line,count)\n",
    "    line+=1\n",
    "\n",
    "workbook.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            申购人数\n",
       "上市日期            \n",
       "2016-02-29     0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>申购人数</th>\n    </tr>\n    <tr>\n      <th>上市日期</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2016-02-29</th>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 56
    }
   ],
   "source": [
    "df_m[df_m['申购人数']==0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}